using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using xl = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Collections;
using System.Reflection;
using Deloitte.Windows.Forms;
using Deloitte.Common;
namespace Deloitte.Data
{
	/// <summary>
	/// An enhanced version of the System.Data.DataTable
	/// </summary>	
	public class DataTable2:System.Data.DataTable
	{
		#region "Declarations"
        private DataGridViewForm frmDataGridView;
        private DataForm dataForm;
		//private Hannon.Forms.FormPatient frmPatient;
		#endregion
		#region "Constructors"

        public DataTable2()
        {
            
        }
		public DataTable2(DataTable table)
		{
			this.SetDataTableColumnsRows(table);
		}
        public DataTable2(DataGridView dataGridView)
        {
            this.SetDataTableColumnsRows(Utility.CreateDataTable(dataGridView));
        }
        public DataTable2(DataView dataView)
        {
            this.SetDataTableColumnsRows(Utility.CreateDataTable(dataView));
        }
 
        public DataTable2(String CsvFile, bool hasHeaders)
        {
            this.SetDataTableColumnsRows(Utility.CreateDataTable(CsvFile, hasHeaders));
        }
		#endregion
		#region "Private Methods"

		private void SetDataTableColumnsRows(DataTable myDataTable)
		{
			foreach(DataColumn myDataColumn in myDataTable.Columns)
			{
				this.Columns.Add(myDataColumn.ColumnName, myDataColumn.DataType);
			}
			foreach(DataRow myDataRow in myDataTable.Rows)
			{
				this.ImportRow(myDataRow);
			}			
		}
		#endregion
		#region "Public Methods"

		public DataTable2 Filter(string FilterStatement, string [] ColumnNames)
		{
			return this.Filter(FilterStatement, ColumnNames, string.Empty);
		}
		public DataTable2 Filter(string FilterStatement, string [] ColumnNames, string SortField)
		{
			DataTable2 myDataTable = new DataTable2();
			DataRow[] rowsMatching;							
			DataRow newRow;							
			try
			{			
				//use the base Select method to extract desired rows
				if (SortField != null && SortField.Length > 0) 
				{
					rowsMatching = this.Select(FilterStatement, SortField);
				}
				else //do not use a sort field
				{
					rowsMatching = this.Select(FilterStatement);
				}
				//add columns to the new table
				if(ColumnNames != null)
				{
					foreach(string ColName in ColumnNames)
					{
						if (ColName != null && ColName.Length > 0)
						{
							if (this.Columns.Contains(ColName))
							{
								myDataTable.Columns.Add(ColName, this.Columns[ColName].DataType);
							}
						}
					}
					foreach(DataRow matchedRow in rowsMatching)
					{
						newRow = myDataTable.NewRow();
						int z= 0;
						foreach(string ColName in ColumnNames)
						{
							if (ColName != null && ColName.Length > 0)
							{
								newRow[z] = matchedRow[ColName];
								z++;
							}
						}
						myDataTable.Rows.Add(newRow);
					}
				}
				else
				{
					int i = 0;
					foreach(DataColumn myDataColumn in this.Columns)
					{
						myDataTable.Columns.Add(myDataColumn.ColumnName, myDataColumn.DataType);
						i++;
					}
					foreach(DataRow matchedRow in rowsMatching)
					{
						myDataTable.ImportRow(matchedRow);
					}
				}
			}

			catch(Exception ex)
			{
				throw new Exception(ex.ToString());
			}
			return myDataTable;
		}

		public DataTable2 Filter(string filterStatement, int[] columnNumbers)
		{
			return this.Filter(filterStatement, columnNumbers, string.Empty);
		}
		public DataTable2 Filter(string filterStatement, int[] columnNumbers, string sortField)
		{			
			string[] columnNames =  new string[columnNumbers.Length-1];
			for(int i=0; i < columnNumbers.Length - 1; i++)
			{
				columnNames[i] = this.Columns[columnNumbers[i]].ColumnName;
			}
			return this.Filter(filterStatement, columnNames, sortField);			
		}
        
		public void ExportToExcel(xl.Worksheet sheet, xl.Range startingRange, bool isColumnHeadersIncluded, bool isPatientFormShowing, string patientFormComment, bool addAutoFilter)
		{
			this.ExportToExcel(sheet, startingRange, 1, 1, isColumnHeadersIncluded, null, null, isPatientFormShowing, patientFormComment,addAutoFilter);
		}
		public void ExportToExcel(xl.Worksheet sheet, xl.Range startingRange,int rowOffset, int columnOffset, bool isColumnHeadersIncluded, string[] xlColumnWidths, string[] xlNumberFormats, bool isPatientFormShowing, string patientFormComment, bool addAutoFilter)
		{
			Utility.ExportToExcel(this,sheet,  startingRange,rowOffset, columnOffset,isColumnHeadersIncluded, xlColumnWidths, xlNumberFormats, isPatientFormShowing, patientFormComment, addAutoFilter);
		}
		public void ReorderColumns(string[] myColumnOrder)
		{			
			DataTable myNewDataTable = new DataTable();			
			string myCol;
			try
			{
				foreach(string myString in myColumnOrder)
				{
					myCol = myString;
					if (myCol==null || myCol.Trim() == "") goto NextColumn;				
					foreach(DataColumn myDataColumn in this.Columns)
					{
						if (myDataColumn.ColumnName == myCol)
						{
							if (myNewDataTable.Columns.Contains(myCol)==false)
							{
								myNewDataTable.Columns.Add(myDataColumn.ColumnName, myDataColumn.DataType);
								goto NextColumn;
							}
						}
					}
				NextColumn:;														
				}
				DataRow myReorderedRow ;
				foreach(DataRow myDataRow in this.Rows)
				{
					myReorderedRow = myNewDataTable.NewRow();
					foreach(string myString in myColumnOrder)
					{
						myCol = myString;
						foreach(DataColumn myDataColumn in this.Columns)
						{
							if (myDataColumn.ColumnName == myCol)
							{
								myReorderedRow[myCol] = myDataRow[myDataColumn.ColumnName];
								goto NextReorderedColumn;
							}
						}
					NextReorderedColumn:;
					}
					myNewDataTable.Rows.Add(myReorderedRow);
				}
				this.Clear();
				this.Columns.Clear();
				foreach(DataColumn myDataColumn in myNewDataTable.Columns)
				{
					if (!this.Columns.Contains(myDataColumn.ColumnName)) this.Columns.Add(myDataColumn.ColumnName, myDataColumn.DataType);
				}
				foreach(DataRow myDataRow in myNewDataTable.Rows) this.ImportRow(myDataRow);

			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
		}
		public void ReplaceColumnValues(string[] columnNames,object replaceValue, object newValue)
		{
			try
			{
				foreach(string myString in columnNames)
				{
					this.ReplaceColumnValues(myString, replaceValue, newValue);
				}
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
		}
		public void ReplaceColumnValues(int[] columnNumbers,object replaceValue, object newValue)
		{
			string colName;
			try
			{
				foreach(int myColNumber in columnNumbers)
				{
					colName=this.Columns[myColNumber].ColumnName;
					this.ReplaceColumnValues(colName, replaceValue, newValue);
				}
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
		}
		public void ReplaceColumnValues(string columnName,object replaceValue, object newValue)
		{
			try
			{
				foreach(DataRow myDataRow in this.Rows)
				{
					if(replaceValue.Equals(myDataRow[columnName]))
					{
						myDataRow[columnName]=newValue;
					}
				}
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}
		}
		public string[] SelectDistinctColumnValues(string columnName)
		{
			try
			{				
				System.Collections.Specialized.StringCollection myVals = new System.Collections.Specialized.StringCollection();
				foreach (DataRow myDataRow in this.Rows)
				{												
					if (!myVals.Contains(myDataRow[columnName].ToString()))
					{
						myVals.Add(myDataRow[columnName].ToString());
					}							
				}
				string[] thisArray = new string[myVals.Count];
				for(int i=0; i<myVals.Count; i++)
				{
					thisArray[i] = myVals[i];
				}
				return thisArray;
			}
			catch (Exception ex)
			{
				throw new Exception(ex.ToString());
			}

		}
		public string[] SelectDistinctColumnValues(string[] colNames)
		{			
			string[] tmpArray;
			System.Collections.Specialized.StringCollection myVals = new System.Collections.Specialized.StringCollection();
			foreach (string myColumnName in colNames)
			{			
				tmpArray = this.SelectDistinctColumnValues(myColumnName);		
				for (int i=0;i<tmpArray.Length; i++)
				{
					if (!myVals.Contains(tmpArray[i]))
					{
						myVals.Add(tmpArray[i]);
					}
				}				
			}
			string[] thisArray = new string[myVals.Count-1];
			for(int i=0; i<myVals.Count; i++)
			{
				thisArray[i] = myVals[i];
			}
			return thisArray;
		}
		public double Sum(string ColumnName)
		{
			try
			{
				string[] ColumnNames = {ColumnName};
				return this.Sum(ColumnNames);
			}
			catch(Exception ex){throw new Exception(ex.ToString());}
		}
		public double Sum(int[] ColumnNumbers)
		{
			string[] ColumnNames =  new string[ColumnNumbers.Length -1];
			for (int i = 0; i < ColumnNames.Length-1; i++)
			{
				ColumnNames[i] = this.Columns[i].ColumnName;
			}
			return Sum(ColumnNames);
		}
		public double Sum(string[] ColumnNames)
		{
			double RunningSum = 0.00;
			foreach(DataRow myDataRow in this.Rows)
			{
				try
				{
					for (int i=0; i<ColumnNames.Length; i++)
					{
						RunningSum += System.Convert.ToDouble(myDataRow[ColumnNames[i]].ToString());
					}
				}
				catch(Exception ex)
				{
					throw new Exception(ex.ToString());
				}
			}
			return RunningSum;
		}
		public bool HasRows()
		{
			try
			{
				if(this.Rows.Count>0)
				{
					return true;
				}
				else return false;
			}
			catch
			{
				return false;
			}
		}
		public void ConvertColumnDataType(int colNumber, System.TypeCode newDataType)
		{
			try
			{
				if (!this.Columns.CanRemove(this.Columns[colNumber])) return;
				System.Collections.Specialized.StringCollection tmpCols = new System.Collections.Specialized.StringCollection();			
				string[] colOrder = new string[this.Columns.Count];
				string columnName = this.Columns[colNumber].ColumnName;
				int i = 0;
				foreach(DataColumn dc in this.Columns){
					colOrder[i]=(dc.ColumnName);
					i++;
				}		
				ArrayList rowList = new ArrayList();
				foreach(DataRow dr in this.Rows){rowList.Add(dr[colNumber]);}
				this.Columns.Remove(this.Columns[colNumber]);
				switch (newDataType)
				{
						case TypeCode.String:
						{
							this.Columns.Add(columnName,System.Type.GetType("string"));
							break;
						}
					case TypeCode.Int16:{
						this.Columns.Add(columnName, System.Type.GetType("Int16"));
						break;
					}
						//TODO: Chnage the System.GetType to typeof
                    case TypeCode.Int32:{
						this.Columns.Add(columnName, typeof(Int32));
						break;
					}
					case TypeCode.Int64:{
						this.Columns.Add(columnName, System.Type.GetType("Int64"));
						break;
					}
                    case TypeCode.UInt16:{
						this.Columns.Add(columnName, System.Type.GetType("UInt16"));
						break;
					}
                    case TypeCode.UInt32:{
						this.Columns.Add(columnName, System.Type.GetType("UInt32"));
						break;
					}
                    case TypeCode.UInt64:{
						this.Columns.Add(columnName, typeof(UInt64));
						break;
					}
                    case TypeCode.Byte:{
						this.Columns.Add(columnName, typeof(byte));
						break;
					}
					case TypeCode.SByte:
					{
						this.Columns.Add(columnName, typeof(sbyte));
						break;
					}
                    case TypeCode.Boolean:{
						this.Columns.Add(columnName, typeof(bool));
						break;
					}
                    case TypeCode.DateTime:{
						this.Columns.Add(columnName, typeof(DateTime));
						break;
					}                                                
                    case TypeCode.Decimal:{
						this.Columns.Add(columnName, typeof(Decimal));
						break;
					}
                    case TypeCode.Double:{
						this.Columns.Add(columnName, System.Type.GetType("Double"));
						break;
					}                                                
					case TypeCode.Char:
					{
						this.Columns.Add(columnName, System.Type.GetType("Char"));
						break;
					}
					case TypeCode.Object:
					{
						this.Columns.Add(columnName, System.Type.GetType("Object"));
						break;
					}
                    case TypeCode.Single:{
                        this.Columns.Add(columnName, System.Type.GetType("Single"));
						break;}
					default:
					{
						this.Columns.Add(columnName, System.Type.GetType("Object"));
						break;
					}						
				}	
				DataRow myDataRow;
									
					for(i=0;i<this.Rows.Count;i++)
					{
						myDataRow = this.Rows[i];
						try
						{							
							switch (newDataType)
							{
								case TypeCode.String:
								{
									myDataRow[columnName] = (string)rowList[i];
									break;
								}
								case TypeCode.Int16:
								{
									myDataRow[columnName] = System.Convert.ToInt16(rowList[i]);
									break;
								}
								case TypeCode.Int32:
								{
									myDataRow[columnName] = System.Convert.ToInt32(rowList[i]);
									break;
								}
								case TypeCode.Int64:
								{
									myDataRow[columnName] = System.Convert.ToInt64(rowList[i]);
									break;
								}
								case TypeCode.UInt16:
								{
									myDataRow[columnName] = System.Convert.ToUInt16(rowList[i]);
									break;
								}
								case TypeCode.UInt32:
								{
									myDataRow[columnName] = System.Convert.ToUInt32(rowList[i]);
									break;
								}
								case TypeCode.UInt64:
								{
									myDataRow[columnName] = System.Convert.ToUInt64(rowList[i]);
									break;
								}
								case TypeCode.Byte:
								{
									myDataRow[columnName] = (byte)rowList[i];
									break;
								}
								case TypeCode.SByte:
								{
									myDataRow[columnName] = (sbyte)rowList[i];
									break;
								}
								case TypeCode.Boolean:
								{
									myDataRow[columnName] = (bool)rowList[i];
									break;
								}
								case TypeCode.DateTime:
								{
									myDataRow[columnName] = System.Convert.ToDateTime(rowList[i]);
									break;
								}                                                
								case TypeCode.Decimal:
								{
									myDataRow[columnName] = System.Convert.ToDecimal(rowList[i]);
									break;
								}
								case TypeCode.Double:
								{
									myDataRow[columnName] = System.Convert.ToDouble(rowList[i]);
									break;
								}                                                
								case TypeCode.Char:
								{
									myDataRow[columnName] = System.Convert.ToChar(rowList[i]);
									break;
								}
								case TypeCode.Object:
								{
									myDataRow[columnName] = (object)rowList[i];
									break;
								}
								case TypeCode.Single:
								{
									myDataRow[columnName] = System.Convert.ToSingle(rowList[i]);
									break;}
								default:
								{
									myDataRow[columnName] = (object)rowList[i];
									break;
								}						
							}	
						}
						catch{myDataRow[columnName] = DBNull.Value;}	
				}						
				this.ReorderColumns(colOrder);
			}				
			catch(Exception ex){
                throw new Exception(ex.ToString());
            }
		}
		
	
		public void ShowInDataForm()
		{
			try
			{
                DataForm dataForm = new DataForm(this);
                dataForm.Show();
			}
			catch(Exception ex){throw new Exception(ex.ToString());}
		}
		#endregion
	}
}
